import delimited using "legislatordiversity.com_data.csv", clear 
rename electionyear year
statastates, name(state)
rename state state_name
replace state_name = proper(state_name)
bro if _merge == 2
drop _merge

fre assignedraceethnicityoflegislato
gen black = . 
replace black = 1 if assignedraceethnicityoflegislato == "Black"
replace black = 0 if assignedraceethnicityoflegislato != "Black"

fre legislativebranch
gen house = . 
replace house = 1 if legislativebranch == "House Of Representatives"
replace house = 0 if legislativebranch != "House Of Representatives"


drop if house !=1
drop if year != 2020

fre district
tab district
bro district
destring district, replace force

collapse (mean) black, by(state_name year)
statastates, name(state_name)
replace state_name = proper(state_name)
bro if _merge == 2
drop _merge

rename black perc_black_house

save "Legislative Diversity Data Cleaned and Collapsed.dta", replace